﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class GroupTopicDAL
    {
        private const string Waming = "SqlServerDAL.GroupTopicDAL";

        public int Save(GroupTopicDto dto)
        {
            try
            {
                int returnValue;
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@GtTitle", SqlDbType.VarChar) {Value = dto.GtTitle},
                                         new SqlParameter("@GtSponsor", SqlDbType.Int) {Value = dto.GtSponsor},
                                         new SqlParameter("@GtTime", SqlDbType.DateTime) {Value = dto.GtTime},
                                         new SqlParameter("@GtGroupId", SqlDbType.VarChar) {Value = dto.GtGroupId}
                                     };
                if (dto.GtId == 0)
                {
                    sql =
                        @"insert into GroupTopic(GtTitle,GtSponsor,GtTime,GtGroupId) values(@GtTitle,@GtSponsor,@GtTime,
                        @GtGroupId) select @@identity as resultId";
                    returnValue = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()));
                }
                else
                {
                    sql = @"update GroupTopic set GtTitle=@GtTitle,GtSponsor=@GtSponsor,GtTime=@GtTime,GtGroupId=@GtGroupId where GtId=@GtId";
                    parameters.Add(new SqlParameter("@GtId", SqlDbType.Int) { Value = dto.GtId });
                    returnValue = SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                }
                return returnValue;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(GroupTopicDto dto)", ex);
                return -1;
            }
        }

        public Hashtable FindById(int id)
        {
            try
            {
                Hashtable ht = null;
                const string sql = "select * from GroupTopic where GtId=@GtId";
                var parame = new SqlParameter("@GtId", SqlDbType.Int) { Value = id };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parame))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindById(int id)", ex);
                return null;
            }
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, int groupId, string order)
        {
            try
            {
                var hts = new List<Hashtable>();
                order = string.IsNullOrEmpty(order) ? "order by GtTime desc" : order;
                var sql = @"select row_number() over (" + order + ") as rowNumber,* from GroupTopic ";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (groupId != 0)
                {
                    whereSql += " where GtGroupId=@GtGroupId";
                    parameters.Add(new SqlParameter("@GtGroupId", SqlDbType.Int) { Value = groupId });
                }
                sql = string.Format(
                    "select * from ({0} {1}) as GroupTopic where rowNumber between {2} AND {3}",
                    sql, whereSql, firstResult, maxResult);
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray())
                    )
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SlicedFindAll(int firstResult, int maxResult, int groupId, string order)", ex);
                return null;
            }
        }

        public List<Hashtable> FindAll(int groupId, string[] channelIds)
        {
            try
            {
                var hts = new List<Hashtable>();
                const string sql = "select * from GroupTopic g inner join SportsGroup s on s.GroupId=g.GtGroupId";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (groupId != 0)
                {
                    whereSql += " where s.GtGroupId=@GtGroupId";
                    parameters.Add(new SqlParameter("@GtGroupId", SqlDbType.Int));
                }
                if (channelIds != null)
                {
                    var tempStr = "";
                    for (var i = 0; i < channelIds.Length; i++)
                    {
                        tempStr += tempStr == ""
                                       ? "s.GroupChannel like @GroupChannel" + i
                                       : " or s.GroupChannel like @GroupChannel";
                        parameters.Add(new SqlParameter("@GroupChannel" + i, SqlDbType.Int) { Value = "%" + channelIds[i] + "%" });
                    }
                    if (tempStr != "")
                    {
                        whereSql += whereSql == "" ? " where (" + tempStr + ")" : " and (" + tempStr + ")";
                    }
                }
                using (
                    var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                      parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindAll(int groupId, string[] channelIds)", ex);
                return null;
            }
        }

        public int SelectCount(int groupId)
        {
            try
            {
                const string sql = "select count(*) from GroupTopic";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (groupId != 0)
                {
                    whereSql += " where GtGroupId=@GtGroupId";
                    parameters.Add(new SqlParameter("@GtGroupId", SqlDbType.Int) { Value = groupId });
                }
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql + whereSql, parameters.ToArray());
                return Convert.ToInt32(red);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectCount(int groupId)", ex);
                return -1;
            }
        }
    }
}
